package com.han.jdbc.mysql.commons_dbutils.demo;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import com.han.jdbc.mysql.bean.User;
import com.han.jdbc.mysql.uils.JDBCUtils;

/**
* @author hanpeng
* @version 创建时间：2020年7月5日 下午11:19:08
* @ClassName 类名称
* @Description 类描述
*/
public class TestDbUtils {
	@Test
	public void test1()  {
		//SELECT * FROM Person WHERE name=?
		QueryRunner run = new QueryRunner();
		String sql="SELECT * FROM `order`";
		Object[] result=null;
		Connection conn=null;
		try {
			conn = JDBCUtils.getConnection();
			result = run.query(JDBCUtils.getConnection(), sql, new ResultSetHandlerIml());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		for (int i = 0; i < result.length; i++) {
			System.out.println(result[i]);
		}
			    
	}
	
	@Test
	public void testWrite()  {
		//SELECT * FROM Person WHERE name=?
		QueryRunner run = new QueryRunner();
		String sql="INSERT INTO Person (name,height) VALUES (?,?)";
		int result=0;
		Connection conn=null;
		try {
			conn = JDBCUtils.getConnection();
			result = run.update(conn, sql, "John Doe", 1.82);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
			    
	}
	
	@Test
	public void testResultSetHandler()  {
		QueryRunner run = new QueryRunner();
		ResultSetHandler<List<User>> h = new BeanListHandler<User>(User.class);
		Connection conn=null;
		String sql="SELECT * FROM `order`";
		try {
			List<User> users = run.query(conn, sql, h);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	@Test
	public void testBean()  {
		QueryRunner run = new QueryRunner();
		ResultSetHandler<User> h = new BeanHandler<User>(User.class);
		Connection conn=null;
		String sql="SELECT * FROM `order`";
		try {
			User users = run.query(conn, sql, h);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				//是需要手动关闭连接的  run.query 调用这个方法 没有帮我们关闭 我们也可以调用帮我们关闭的 query
				//关闭连接
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
